package com.lanqiao.guimei.dao;
import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.commons.ProductListHandler;
import com.lanqiao.guimei.vo.Page;
import com.lanqiao.guimei.vo.Product;
import com.lanqiao.guimei.vo.SaleProd;
import com.lanqiao.guimei.vo.Stock;
import com.lanqiao.web.mvc.annotation.RequestMapping;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
 * @Author:沈林玉
 * @date:2019/8/13 21:13
 * @Description:商品管理dao类
 */

public class ProductDao extends BaseDao{
    Logger logger = Logger.getLogger(ProductDao.class);

    /**
     * 1.商品查询,分页显示
     * @param page
     * @return
     */
    public List<Product> selectProduct(Page<Product> page) {

        String sql = "select p.prod_no prodNo,p.ctgr_id ctgrId,p.ven_id venId, " +
                "p.stk_id stkId,p.prod_name prodName,p.price,p.type,p.descr,p.image,p.rn " +
                "from (select t.*,ROWNUM rn from (select * from PRODUCT order by PROD_NO) t where ROWNUM<=?) p " +
                "where rn>=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            return runner.query(conn,sql,new BeanListHandler<>(Product.class),page.getLastIndex(),page.getFirstIndex());
        } catch (SQLException e) {
            logger.error("商品分页查询失败。。。"+e.getMessage());
        }
        return null;
    }

    /**
     * 获得总记录数
     * @param page
     * @return
     */
    public int getCountProduct(Page<Product> page){
        String sql = "select count(1) ptn from product";
        try (Connection conn = JdbcUtil.getConnection()) {
            return runner.query(conn,sql,new ScalarHandler<BigDecimal>("ptn")).intValue();

        } catch (SQLException e) {
            logger.error("获取商品总记录数失败。。。"+e.getMessage());
        }
        return 0;
    }

    /**
     * 2.新增商品
     * @param product
     * @return
     */
    public boolean addProduct(Product product){
        String sql = "insert into product(prod_no,ctgr_id,ven_id,stk_id,prod_name,price,type,descr,image) " +
                "values(sys_guid(),?,?,?,?,?,?,?,?)";
        try (Connection conn = JdbcUtil.getConnection()) {
            int row = runner.update(conn, sql, product.getCtgrId(), product.getVenId(), product.getStkId(),
                    product.getProdName(), product.getPrice(), product.getType(),
                    product.getDescr(), product.getImage());

            return row==1?true:false;
        } catch (SQLException e) {
            logger.error("插入商品失败。。。"+e.getMessage());
        }

        return false;
    }

    /**
     * 3.更新商品
     * @param product
     * @return
     */
    @RequestMapping("/product/doUpdate")
    public boolean updateProduct(Product product){
        String sql = "update product set prod_name=?,price=?,type=?,descr=?,image=? where prod_no=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            int row = runner.update(conn, sql, product.getProdName(), product.getPrice(), product.getType(),
                    product.getDescr(), product.getImage(),product.getProdNo());
            return row==1?true:false;

        } catch (SQLException e) {
            logger.error("更新商品失败。。。"+e.getMessage());
        }
        return false;
    }

    /**
     * 4.下架在线商品
     * @param product
     * @return
     */
    public boolean deleteProduct(Product product){
        String sql = "update sale_prod set status =? where id=?";
        try (Connection conn = JdbcUtil.getConnection()) {
            int row = runner.update(conn, sql, product.getProdNo());
            return row==1?true:false;
        } catch (SQLException e) {
            logger.error("下架在线商品失败。。。"+e.getMessage());
        }
        return false;
    }

    /**
     * 5.按主键查询商品
     * @param product
     * @return
     */
    public List<Product> selectProductById(Product product) {
        String sql = "select p.prod_no prodNo," +
                            "p.ctgr_id ctgrId," +
                            "p.ven_id venId," +
                            "p.stk_id stkId," +
                            "p.CTGR_NAME ctgName," +
                            "p.VEN_NAME venName," +
                            "p.STK_NAME stkName," +
                            "p.prod_name proName," +
                            "p.quantity quantity," +
                            "p.price," +
                            "p.type," +
                            "p.descr," +
                            "p.image  " +
                            "from (select distinct pro.* ,cate.CTGR_NAME,stk.STK_NAME,stk.quantity,ven.VEN_NAME from product pro,category cate,stock stk,vendor ven " +
                            "where pro.CTGR_ID=cate.CTGR_ID and pro.STK_ID=stk.STK_ID and pro.VEN_ID=ven.VEN_ID and pro.prod_no=?) p";
        try (Connection conn = JdbcUtil.getConnection()) {
            return runner.query(conn,sql,new ProductListHandler(),product.getProdNo());
        } catch (SQLException e) {
            logger.error("按主键查询商品失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 6.按条件查询商品信息
     * @param page
     * @return
     */
    public List<Product> selectProductByCondition(Page<Product> page) {
        Product product = page.getQueryObject();
        List productList = new ArrayList();
        StringBuffer sql = new StringBuffer("select p.prod_no prodNo," +
                "p.ctgr_id ctgrId," +
                "p.ven_id venId," +
                "p.stk_id stkId," +
                "p.CTGR_NAME ctgName," +
                "p.VEN_NAME venName," +
                "p.STK_NAME stkName," +
                "p.prod_name proName," +
                "p.quantity quantity," +
                "p.price," +
                "p.type," +
                "p.descr," +
                "p.image," +
                "p.remark"+
                " from (select r.*,ROWNUM rn from" +
                "(select distinct pro.* ,cate.CTGR_NAME,stk.STK_NAME,stk.quantity,ven.VEN_NAME from product pro,category cate,stock stk,vendor ven " +
                "where pro.CTGR_ID=cate.CTGR_ID and pro.STK_ID=stk.STK_ID and pro.VEN_ID=ven.VEN_ID ");

        if (StringUtils.isNotBlank(product.getProdName())){
            sql.append(" and pro.prod_name like ? ");
            productList.add("%"+product.getProdName()+"%");
        }

        if (StringUtils.isNotBlank(product.getType())){
            sql.append(" and pro.type = ? ");
            productList.add(product.getType());
        }

        if (product.getCategory()!=null &&
                StringUtils.isNotBlank(product.getCategory().getBigCtgr())){
            sql.append(" and pro.ctgr_id=" +
                    " (select ctgr_id from category where big_ctgr =? ");
            productList.add(product.getCategory().getBigCtgr());
        }
        if (product.getVendor()!=null &&
                StringUtils.isNotBlank(product.getVendor().getVenName())){
            sql.append(" and pro.ven_id=" +
                    " (select ven_id from vendor where ven_name =? ");
            productList.add(product.getVendor().getVenName());
        }

        if (product.getPrice()!=null){
            sql.append(" and pro.price >= ?");
            productList.add(product.getPrice());
        }
        sql.append(" )r where ROWNUM<=?"+
                " ) p where rn>=?");
        productList.add(page.getLastIndex());
        productList.add(page.getFirstIndex());
        String sql1 = sql.toString();
        try (Connection conn = JdbcUtil.getConnection()) {// list.toArray
            List<Product> products = runner.query(conn, sql1, new ProductListHandler(),productList.toArray());
            return products;
        } catch (SQLException e) {
            logger.error("商品按条件查询失败。。。"+e.getMessage());
        }

        return null;
    }

    /**
     * 7.根据商品编号prod_no ，查出商品信息和“卖家”的“姓名”和“id”
     * @param product
     * @return
     */
    public Product ProductById(Product product){
        StringBuffer sql = new StringBuffer("select p.prod_no prodNo,p.CTGR_ID ctgrId,p.VEN_ID venId,p.STK_ID stkId,p.PROD_NAME prodName,p.PRICE,p.TYPE,p.DESCR,p.IMAGE,s.saler_id salerId,g.login_name guimeiUsers,s.real_price realPrice " +
                " from  PRODUCT p,sale_prod s,GUIMEI_USERS g " +
                " where p.PROD_NO=? and p.prod_no=s.prod_no and s.saler_id= g.user_id ");
        try {
            return runner.query(sql.toString(),new BeanHandler<>(Product.class),product.getProdNo() );

        } catch (SQLException e) {
            logger.error("查出商品信息失败. ", e);
        }
        return null;
    }

    /**
     * 8.根据商品编号prod_no ，查出商品信息
     * @param product
     * @return
     */
    public Product ProductById1(Product product){
        StringBuffer sql = new StringBuffer("select p.prod_no prodNo,p.CTGR_ID ctgrId,p.VEN_ID venId,p.STK_ID stkId,p.PROD_NAME prodName,p.PRICE,p.TYPE,p.DESCR,p.IMAGE " +
                " from  PRODUCT p,sale_prod s" +
                " where p.PROD_NO=? ");
        try {
            return runner.query(sql.toString(),new BeanHandler<>(Product.class),product.getProdNo() );

        } catch (SQLException e) {
            logger.error("查出商品信息失败. ", e);
        }
        return null;
    }

    /**
     * 9.上架商品到在线销售
     * @param product
     * @return
     */
    public String putawayProduct(Product product,List<SaleProd> saleProds) {
        Connection conn = JdbcUtil.getConnection();

        try {
            String prodNo = product.getProdNo();
            conn.setAutoCommit(false);
            String productSql = "update product set remark=1 where prod_no =?";
            int updateProduct = runner.update(conn, productSql, prodNo);

            String saleProdSql = "insert into sale_prod values(sale_prod_seq.nextval,?,?,?,?,?,?,?,?,?,?)";
//            Object[][] values = new Object[saleProds.size()][10];
//            for (int i = 0; i <values.length ; i++) {
//                SaleProd saleProd = saleProds.get(i);
//                values[i] = new Object[]{
//                        saleProd.getProdNo(),
//                        74,
//                        saleProd.getStkId(),
//                        saleProd.getRealPrice(),
//                        saleProd.getCarriage(),
//                        saleProd.getStarTime(),
//                        saleProd.getEndTime(),
//                        saleProd.getProdSkin(),
//                        saleProd.getLocation(),
//                        1
//                };
//          }
//                int[] batchInserted = runner.batch(conn, saleProdSql, values);
            SaleProd saleProd = saleProds.get(0);
            runner.update(conn,
                    saleProdSql,
                    saleProd.getProdNo(),
                    74,
                    saleProd.getStkId(),
                    saleProd.getRealPrice(),
                    saleProd.getCarriage(),
                    new java.sql.Date(saleProd.getStarTime().getTime()),
                    new java.sql.Date(saleProd.getEndTime().getTime()),
                    saleProd.getProdSkin(),
                    saleProd.getLocation(),
                    1);

                conn.commit();
            return prodNo;

        } catch (SQLException e) {
            logger.error("商品上架失败。。。"+e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException e1) {
                logger.error("商品上架失败。。。"+e1.getMessage());
            }

        }
        return null;
    }

//java.sql.SQLException: ORA-00928: 缺失 SELECT 关键字
// Query: insert into sale_prod values(sale_prod_seq.nextval,?,?,?,?,?,?,?,?,?,?) Parameters: [F2DA7461902043DF9463EFAB7235E4B0, 74, 24, 456.0, 10, 2019-08-03, 2019-08-04, 全新, 南宁, 1]

    public static void main(String[] args) {
        ProductDao productDao = new ProductDao();
        /*int countProduct = productDao.getCountProduct(new Page<>());
        System.out.println(countProduct);
        System.out.println(productDao.selectProduct(new Page<>()));

        boolean b = productDao.addProduct(new Product(null,1002,1,1,"电子产品",2599.99,"联想Y450A-TFO","512独显超强性价比","product/show1_big.jpg"));
        if (b){
            System.out.println("插入成功。。。");
        }else{
            System.out.println("插入失败。。。");
        }*/

        /*boolean b = productDao.updateProduct(new Product("D57660C126D945F7ABDF2914E987C352",1002,1,1,"电子",2599.99,"123","512","product/show1_big.jpg"));
        if (b){
            System.out.println("更新成功。。。");
        }else {
            System.out.println("更新失败。。。");
        }*/

       /* boolean b = productDao.deleteProduct(new Product("D57660C126D945F7ABDF2914E987C352",null,null,null,null,null,null,null,null));
        if (b){
            System.out.println("删除成功。。。");
        }else {
            System.out.println("删除失败。。。");
        }
*/
    }



}
